using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using emp.IDAL;
using emp.Model;
namespace emp.SqlServerDAL
{
    public class Brand : IBrand
    {
        public IList getBrandModelList(string brand, string model)
        {
            string sql = "select cbm_uid,isnull(cbm_brand,''),isnull(cbm_model,''),isnull(cbm_price,'') from CAR_BRAND_MODEL where 1=1";
            if (brand != "")
            {
                sql += " and cbm_brand like '%"+brand+"%'";
            }
            if (model != "")
            {
                sql += " and cbm_model like '%"+model+"%'";
            }
            IList data = new ArrayList();
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, null))
            {
                while (rdr.Read())
                {
                    BrandInfo info = new BrandInfo();
                    info.CBM_UID = rdr.GetInt32(0);
                    info.CBM_BRAND = rdr.GetString(1);
                    info.CBM_MODEL = rdr.GetString(2);
                    info.CBM_PRICE = rdr.GetString(3);
                    data.Add(info);
                }
            }
           
            return data;
        }
        public void getBrandModelDel(int cbm_uid)
        {
            string sql = "delete  from  CAR_BRAND_MODEL  where cbm_uid=" + cbm_uid ;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_DTC_INV))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql,null);
                        trans.Commit();

                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        System.Console.WriteLine(err.Message);
                    }


                }

            }
        }
        public BrandInfo getBrandModelInfo(int cbm)
        {
            BrandInfo info = new BrandInfo();
            string sql = "select cbm_uid,isnull(cbm_brand,''),isnull(cbm_model,''),isnull(cbm_price,'') from CAR_BRAND_MODEL  where cbm_uid="+cbm;

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, null))
            {
                if (rdr.Read())
                {
                    info.CBM_UID = rdr.GetInt32(0);
                    info.CBM_BRAND = rdr.GetString(1);
                    info.CBM_MODEL = rdr.GetString(2);
                    info.CBM_PRICE = rdr.GetString(3);
                }
            }
            return info;
        }
        public void getBrandModelInsert(BrandInfo info)
        {
            string sql = " insert into CAR_BRAND_MODEL(cbm_brand,cbm_model,cbm_price) values('"+info.CBM_BRAND+"','"+info.CBM_MODEL+"','"+info.CBM_PRICE+"')";
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql, null);
                        trans.Commit();
                    }
                    catch (SqlException e)
                    {
                        trans.Rollback();
                        System.Console.WriteLine(e.Message);
                    }
                }
            }
        }
        public void getBrandModelUpdate(BrandInfo info)
        {
            string sql = " update CAR_BRAND_MODEL set cbm_brand='"+info.CBM_BRAND+"',cbm_model='"+info.CBM_MODEL+"',cbm_price='"+info.CBM_PRICE+"' where cbm_uid="+info.CBM_UID;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql, null);
                        trans.Commit();
                    }
                    catch (SqlException e)
                    {
                        trans.Rollback();
                        System.Console.WriteLine(e.Message);
                    }
                }
            }

        }
        public int getIsBrandModelInsert(BrandInfo info)
        {
            string sql = "select * from CAR_BRAND_MODEL where cbm_brand='"+info.CBM_BRAND+"' and cbm_model='"+info.CBM_MODEL+"'";
            int value = 0;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, null))
            {
                if (rdr.Read())
                {
                    value = 1;
                }
            }
            return value;
        }
        public void getQuestAnswerBrandModel(BrandInfo info)
        {
            string sql = " exec p_insert_quest_brand_model_answer @BRAND,@MODEL,@PRICE";
            SqlParameter[] cellParms = new SqlParameter[] 
           {
           	new SqlParameter("@BRAND",SqlDbType.VarChar,100),
            new SqlParameter("@MODEL",SqlDbType.VarChar,100),
            new SqlParameter("@PRICE",SqlDbType.VarChar,100)
           };
            cellParms[0].Value = info.CBM_BRAND;
            cellParms[1].Value = info.CBM_MODEL;
            cellParms[2].Value = info.CBM_PRICE;

            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql, cellParms);
                        trans.Commit();
                    }
                    catch (SqlException e)
                    {
                        trans.Rollback();
                        System.Console.WriteLine(e.Message);
                    }
                }
            }
        }
     
    }
}
